﻿using System;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data;
using Hangjing.DBUtility;
using Hangjing.Model;

namespace Hangjing.SQLServerDAL
{
    /// <summary>
    /// 物料表
    /// </summary>
    public partial class Devices
    {

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(DevicesInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Devices(");
            strSql.Append("cityid,cityname,sortid,devNO,title,unit,price,pri,intro,stock,addtime,admin,reveint1,reveint2,reveint3,reveint4,revevar1,revevar2,revevar3,revevar4,revedate1,revedate2,revefloat1,revefloat2,delflag,deluser,deltime");
            strSql.Append(") values (");
            strSql.Append("@cityid,@cityname,@sortid,@devNO,@title,@unit,@price,@pri,@intro,@stock,@addtime,@admin,@reveint1,@reveint2,@reveint3,@reveint4,@revevar1,@revevar2,@revevar3,@revevar4,@revedate1,@revedate2,@revefloat1,@revefloat2,@delflag,@deluser,@deltime");
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters =
            {
                new SqlParameter("@cityid", SqlDbType.Int,4) ,
                new SqlParameter("@cityname", SqlDbType.VarChar,256) ,
                new SqlParameter("@sortid", SqlDbType.Int,4) ,
                new SqlParameter("@devNO", SqlDbType.VarChar,50) ,
                new SqlParameter("@title", SqlDbType.VarChar,256) ,
                new SqlParameter("@unit", SqlDbType.VarChar,10) ,
                new SqlParameter("@price", SqlDbType.Float,8) ,
                new SqlParameter("@pri", SqlDbType.Int,4) ,
                new SqlParameter("@intro", SqlDbType.VarChar,256) ,
                new SqlParameter("@stock", SqlDbType.Int,4) ,
                new SqlParameter("@addtime", SqlDbType.DateTime) ,
                new SqlParameter("@admin", SqlDbType.Int,4) ,
                new SqlParameter("@reveint1", SqlDbType.VarChar,4000) ,
                new SqlParameter("@reveint2", SqlDbType.Int,4) ,
                new SqlParameter("@reveint3", SqlDbType.Int,4) ,
                new SqlParameter("@reveint4", SqlDbType.Int,4) ,
                new SqlParameter("@revevar1", SqlDbType.VarChar,256) ,
                new SqlParameter("@revevar2", SqlDbType.VarChar,256) ,
                new SqlParameter("@revevar3", SqlDbType.VarChar,256) ,
                new SqlParameter("@revevar4", SqlDbType.VarChar,256) ,
                new SqlParameter("@revedate1", SqlDbType.DateTime) ,
                new SqlParameter("@revedate2", SqlDbType.DateTime) ,
                new SqlParameter("@revefloat1", SqlDbType.Float,8) ,
                new SqlParameter("@revefloat2", SqlDbType.Float,8) ,
                new SqlParameter("@delflag", SqlDbType.Int,4) ,
                new SqlParameter("@deluser", SqlDbType.VarChar,50) ,
                new SqlParameter("@deltime", SqlDbType.DateTime)

            };

            parameters[0].Value = model.cityid;
            parameters[1].Value = model.cityname;
            parameters[2].Value = model.sortid;
            parameters[3].Value = model.devNO;
            parameters[4].Value = model.title;
            parameters[5].Value = model.unit;
            parameters[6].Value = model.price;
            parameters[7].Value = model.pri;
            parameters[8].Value = model.intro;
            parameters[9].Value = model.stock;
            parameters[10].Value = model.addtime;
            parameters[11].Value = model.admin;
            parameters[12].Value = model.reveint1;
            parameters[13].Value = model.reveint2;
            parameters[14].Value = model.reveint3;
            parameters[15].Value = model.reveint4;
            parameters[16].Value = model.revevar1;
            parameters[17].Value = model.revevar2;
            parameters[18].Value = model.revevar3;
            parameters[19].Value = model.revevar4;
            parameters[20].Value = model.revedate1;
            parameters[21].Value = model.revedate2;
            parameters[22].Value = model.revefloat1;
            parameters[23].Value = model.revefloat2;
            parameters[24].Value = model.delflag;
            parameters[25].Value = model.deluser;
            parameters[26].Value = model.deltime;
            return HJConvert.ToInt32(SQLHelper.ExecuteScalar(CommandType.Text, strSql.ToString(), parameters));
        }


        /// <summary>
        /// 更新一条数据
        /// </summary>
        public int Update(DevicesInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Devices set ");

            strSql.Append(" cityid = @cityid , ");
            strSql.Append(" cityname = @cityname , ");
            strSql.Append(" sortid = @sortid , ");
            strSql.Append(" devNO = @devNO , ");
            strSql.Append(" title = @title , ");
            strSql.Append(" unit = @unit , ");
            strSql.Append(" price = @price , ");
            strSql.Append(" pri = @pri , ");
            strSql.Append(" intro = @intro , ");
            strSql.Append(" stock = @stock , ");
            strSql.Append(" addtime = @addtime , ");
            strSql.Append(" admin = @admin , ");
            strSql.Append(" reveint1 = @reveint1 , ");
            strSql.Append(" reveint2 = @reveint2 , ");
            strSql.Append(" reveint3 = @reveint3 , ");
            strSql.Append(" reveint4 = @reveint4 , ");
            strSql.Append(" revevar1 = @revevar1 , ");
            strSql.Append(" revevar2 = @revevar2 , ");
            strSql.Append(" revevar3 = @revevar3 , ");
            strSql.Append(" revevar4 = @revevar4 , ");
            strSql.Append(" revedate1 = @revedate1 , ");
            strSql.Append(" revedate2 = @revedate2 , ");
            strSql.Append(" revefloat1 = @revefloat1 , ");
            strSql.Append(" revefloat2 = @revefloat2 , ");
            strSql.Append(" delflag = @delflag , ");
            strSql.Append(" deluser = @deluser , ");
            strSql.Append(" deltime = @deltime  ");
            strSql.Append(" where dId=@dId ");

            SqlParameter[] parameters =
            {
                        new SqlParameter("@dId", SqlDbType.Int,4) ,
                        new SqlParameter("@cityid", SqlDbType.Int,4) ,
                        new SqlParameter("@cityname", SqlDbType.VarChar,256) ,
                        new SqlParameter("@sortid", SqlDbType.Int,4) ,
                        new SqlParameter("@devNO", SqlDbType.VarChar,50) ,
                        new SqlParameter("@title", SqlDbType.VarChar,256) ,
                        new SqlParameter("@unit", SqlDbType.VarChar,10) ,
                        new SqlParameter("@price", SqlDbType.Float,8) ,
                        new SqlParameter("@pri", SqlDbType.Int,4) ,
                        new SqlParameter("@intro", SqlDbType.VarChar,256) ,
                        new SqlParameter("@stock", SqlDbType.Int,4) ,
                        new SqlParameter("@addtime", SqlDbType.DateTime) ,
                        new SqlParameter("@admin", SqlDbType.Int,4) ,
                        new SqlParameter("@reveint1", SqlDbType.VarChar,4000) ,
                        new SqlParameter("@reveint2", SqlDbType.Int,4) ,
                        new SqlParameter("@reveint3", SqlDbType.Int,4) ,
                        new SqlParameter("@reveint4", SqlDbType.Int,4) ,
                        new SqlParameter("@revevar1", SqlDbType.VarChar,256) ,
                        new SqlParameter("@revevar2", SqlDbType.VarChar,256) ,
                        new SqlParameter("@revevar3", SqlDbType.VarChar,256) ,
                        new SqlParameter("@revevar4", SqlDbType.VarChar,256) ,
                        new SqlParameter("@revedate1", SqlDbType.DateTime) ,
                        new SqlParameter("@revedate2", SqlDbType.DateTime) ,
                        new SqlParameter("@revefloat1", SqlDbType.Float,8) ,
                        new SqlParameter("@revefloat2", SqlDbType.Float,8) ,
                        new SqlParameter("@delflag", SqlDbType.Int,4) ,
                        new SqlParameter("@deluser", SqlDbType.VarChar,50) ,
                        new SqlParameter("@deltime", SqlDbType.DateTime)

            };

            parameters[0].Value = model.dId;
            parameters[1].Value = model.cityid;
            parameters[2].Value = model.cityname;
            parameters[3].Value = model.sortid;
            parameters[4].Value = model.devNO;
            parameters[5].Value = model.title;
            parameters[6].Value = model.unit;
            parameters[7].Value = model.price;
            parameters[8].Value = model.pri;
            parameters[9].Value = model.intro;
            parameters[10].Value = model.stock;
            parameters[11].Value = model.addtime;
            parameters[12].Value = model.admin;
            parameters[13].Value = model.reveint1;
            parameters[14].Value = model.reveint2;
            parameters[15].Value = model.reveint3;
            parameters[16].Value = model.reveint4;
            parameters[17].Value = model.revevar1;
            parameters[18].Value = model.revevar2;
            parameters[19].Value = model.revevar3;
            parameters[20].Value = model.revevar4;
            parameters[21].Value = model.revedate1;
            parameters[22].Value = model.revedate2;
            parameters[23].Value = model.revefloat1;
            parameters[24].Value = model.revefloat2;
            parameters[25].Value = model.delflag;
            parameters[26].Value = model.deluser;
            parameters[27].Value = model.deltime;
            return SQLHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters);
        }

        /// <summary>
        /// 获取记录所有字段
        /// </summary>
        /// 此代码由杭景科技代码内部生成器自动生成
        /// <param>dId</param>
        /// <returns>DevicesInfo</returns>
        public DevicesInfo GetModel(int dId)
        {
            string sql = "select dId,cityid,cityname,sortid,devNO,title,unit,price,pri,intro,stock,addtime,admin,reveint1,reveint2,reveint3,reveint4,revevar1,revevar2,revevar3,revevar4,revedate1,revedate2,revefloat1,revefloat2,delflag,deluser,deltime from Devices where  dId = @dId";
            SqlParameter parameter = new SqlParameter("@dId", SqlDbType.Int, 4);
            parameter.Value = dId;
            DevicesInfo model = null;
            using (SqlDataReader dr = SQLHelper.ExecuteReader(CommandType.Text, sql, parameter))
            {
                if (dr.Read())
                {
                    model = new DevicesInfo();
                    model.dId = HJConvert.ToInt32(dr["dId"]);
                    model.cityid = HJConvert.ToInt32(dr["cityid"]);
                    model.cityname = HJConvert.ToString(dr["cityname"]);
                    model.sortid = HJConvert.ToInt32(dr["sortid"]);
                    model.devNO = HJConvert.ToString(dr["devNO"]);
                    model.title = HJConvert.ToString(dr["title"]);
                    model.unit = HJConvert.ToString(dr["unit"]);
                    model.price = HJConvert.ToDecimal(dr["price"]);
                    model.pri = HJConvert.ToInt32(dr["pri"]);
                    model.intro = HJConvert.ToString(dr["intro"]);
                    model.stock = HJConvert.ToInt32(dr["stock"]);
                    model.addtime = HJConvert.ToDateTime(dr["addtime"]);
                    model.admin = HJConvert.ToInt32(dr["admin"]);
                    model.reveint1 = HJConvert.ToString(dr["reveint1"]);
                    model.reveint2 = HJConvert.ToInt32(dr["reveint2"]);
                    model.reveint3 = HJConvert.ToInt32(dr["reveint3"]);
                    model.reveint4 = HJConvert.ToInt32(dr["reveint4"]);
                    model.revevar1 = HJConvert.ToString(dr["revevar1"]);
                    model.revevar2 = HJConvert.ToString(dr["revevar2"]);
                    model.revevar3 = HJConvert.ToString(dr["revevar3"]);
                    model.revevar4 = HJConvert.ToString(dr["revevar4"]);
                    model.revedate1 = HJConvert.ToDateTime(dr["revedate1"]);
                    model.revedate2 = HJConvert.ToDateTime(dr["revedate2"]);
                    model.revefloat1 = HJConvert.ToDecimal(dr["revefloat1"]);
                    model.revefloat2 = HJConvert.ToDecimal(dr["revefloat2"]);
                    model.delflag = HJConvert.ToInt32(dr["delflag"]);
                    model.deluser = HJConvert.ToString(dr["deluser"]);
                    model.deltime = HJConvert.ToDateTime(dr["deltime"]);
                }
            }
            return model;
        }

        /// <summary>
        /// 获得总记录数
        /// </summary>
        /// 此代码由杭景科技代码内部生成器自动生成
        public int GetCount(string strWhere)
        {
            return (int)SQLHelper.ExecuteScalar(CommandType.StoredProcedure, "pagecount", new SqlParameter("@tblName", "Devices"), new SqlParameter("@strWhere", strWhere));
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="pagesize">页尺寸</param>
        /// <param name="pageindex">页索引</param>
        /// <param name="strWhere">查询条件</param>
        /// <param name="orderName">排序字段</param>
        /// <param name="orderType">排序类型，1为降序，0为升序</param>
        /// <returns>列表</returns>
        ///此代码由杭景科技代码内部生成器自动生成
        public IList<DevicesInfo> GetList(int pagesize, int pageindex, string strWhere, string orderName, int orderType)
        {

            IList<ShopDatabrandInfo> sortlist = CacheHelper.GetDeviceSorts();

            IList<DevicesInfo> infos = new List<DevicesInfo>();
            SqlParameter[] parameters =
                    {
                new SqlParameter("@tblName", SqlDbType.VarChar,255),
                new SqlParameter("@strGetFields", SqlDbType.VarChar,1000),
                new SqlParameter("@primary", SqlDbType.VarChar,255),
                new SqlParameter("@orderName", SqlDbType.VarChar,255),
                new SqlParameter("@PageSize", SqlDbType.Int),
                new SqlParameter("@PageIndex", SqlDbType.Int),
                new SqlParameter("@OrderType", SqlDbType.Bit),
                new SqlParameter("@strWhere", SqlDbType.VarChar,1500)
            };
            parameters[0].Value = "Devices";
            parameters[1].Value = "dId,cityid,cityname,sortid,devNO,title,unit,price,pri,intro,stock,addtime,admin,reveint1,reveint2,reveint3,reveint4,revevar1,revevar2,revevar3,revevar4,revedate1,revedate2,revefloat1,revefloat2,delflag,deluser,deltime";
            parameters[2].Value = "dId";
            parameters[3].Value = orderName;
            parameters[4].Value = pagesize;
            parameters[5].Value = pageindex;
            parameters[6].Value = orderType;
            parameters[7].Value = strWhere;
            using (SqlDataReader dr = SQLHelper.ExecuteReader(CommandType.StoredProcedure, "pageselectpri", parameters))
            {
                while (dr.Read())
                {
                    DevicesInfo model = new DevicesInfo();
                    model.dId = HJConvert.ToInt32(dr["dId"]);
                    model.cityid = HJConvert.ToInt32(dr["cityid"]);
                    model.cityname = HJConvert.ToString(dr["cityname"]);
                    model.sortid = HJConvert.ToInt32(dr["sortid"]);
                    model.sortname = "";

                    foreach (var item in sortlist)
                    {
                        if (model.sortid == item.ID)
                        {
                            model.sortname = item.classname;
                            break;
                        }
                    }

                    model.devNO = HJConvert.ToString(dr["devNO"]);
                    model.title = HJConvert.ToString(dr["title"]);
                    model.unit = HJConvert.ToString(dr["unit"]);
                    model.price = HJConvert.ToDecimal(dr["price"]);
                    model.pri = HJConvert.ToInt32(dr["pri"]);
                    model.intro = HJConvert.ToString(dr["intro"]);
                    model.stock = HJConvert.ToInt32(dr["stock"]);
                    model.addtime = HJConvert.ToDateTime(dr["addtime"]);
                    model.admin = HJConvert.ToInt32(dr["admin"]);
                    model.reveint1 = HJConvert.ToString(dr["reveint1"]);
                    model.reveint2 = HJConvert.ToInt32(dr["reveint2"]);
                    model.reveint3 = HJConvert.ToInt32(dr["reveint3"]);
                    model.reveint4 = HJConvert.ToInt32(dr["reveint4"]);
                    model.revevar1 = HJConvert.ToString(dr["revevar1"]);
                    model.revevar2 = HJConvert.ToString(dr["revevar2"]);
                    model.revevar3 = HJConvert.ToString(dr["revevar3"]);
                    model.revevar4 = HJConvert.ToString(dr["revevar4"]);
                    model.revedate1 = HJConvert.ToDateTime(dr["revedate1"]);
                    model.revedate2 = HJConvert.ToDateTime(dr["revedate2"]);
                    model.revefloat1 = HJConvert.ToDecimal(dr["revefloat1"]);
                    model.revefloat2 = HJConvert.ToDecimal(dr["revefloat2"]);
                    model.delflag = HJConvert.ToInt32(dr["delflag"]);
                    model.deluser = HJConvert.ToString(dr["deluser"]);
                    model.deltime = HJConvert.ToDateTime(dr["deltime"]);
                    infos.Add(model);
                }
            }
            return infos;
        }

        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <param name="Id"></param>
        /// 此代码由杭景科技代码内部生成器自动生成
        /// <returns></returns>
        public int DelDevices(int Id)
        {
            StringBuilder str = new StringBuilder();
            str.Append("delete from Devices where dId=@dId");
            SqlParameter[] Para =
                {
                new SqlParameter("@dId",SqlDbType.Int)
            };
            Para[0].Value = Id;

            return SQLHelper.ExecuteNonQuery(CommandType.Text, str.ToString(), Para);
        }


        /// <summary>
        /// 批量删除记录
        /// </summary>
        /// <param name="IdList"></param>
        /// <returns></returns>
        /// 此代码由杭景科技代码内部生成器自动生成
        public int DelList(string IdList)
        {
            StringBuilder str = new StringBuilder();
            str.Append("delete from Devices where dId in ({0})");

            return SQLHelper.ExecuteNonQuery(CommandType.Text, string.Format(str.ToString(), IdList), null);
        }




    }
}

